import sqlite3

class DbManager(object):

    def __init__(self, *args):
        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def __enter__(self):
        return self.cursor

    def __exit__(self, types, value, traceback):
        self.db.commit()

        return False

    def __del__(self):
        self.db.commit()
        self.db.close()

    def switchDb(self, *args):
        self.db.close()

        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def createTable(self, tableString):
        self.cursor.execute(tableString)
        self.db.commit()

    def commitAndClose(self):
        self.db.commit()
        self.db.close()

class SQLiteDb(DbManager):

    kuaishouUsers = """
    CREATE TABLE IF NOT EXISTS kuaishouUsers(
        id INTEGER PRIMARY KEY autoincrement,
        phone TEXT,
        userId TEXT,
        userName TEXT,
        userAvatar TEXT
    )"""

    storageVideos = """
    CREATE TABLE IF NOT EXISTS storageVideos(
        id INTEGER PRIMARY KEY autoincrement,
        userId INTEGER default 0,
        userName TEXT default '',
        cityId INTEGER default 0,
        hashCode TEXT,
        filePath TEXT,
        fileSize TEXT,
        title TEXT,
        cover TEXT
    )"""

    publishedVideos = """
    CREATE TABLE IF NOT EXISTS publishedVideos(
        id INTEGER PRIMARY KEY autoincrement,
        userId INTEGER default 0,
        workId TEXT UNIQUE,
        title TEXT,
        cover TEXT,
        uploadTime TEXT,
        playCount INTEGER default 1,
        likeCount INTEGER default 0,
        commentCount INTEGER default 0
    )"""

    _usersMap = {}

    def __init__(self, database='', *args):
        super().__init__(database, *args)
        self.initDb()

    def initDb(self):
        self.createTable(self.kuaishouUsers)
        self.createTable(self.storageVideos)
        self.createTable(self.publishedVideos)

    def addUsers(self, phone, userId, userName, userAvatar=''):
        try:
            insertData = self.cursor.execute("""INSERT INTO kuaishouUsers 
                (phone, userId, userName, userAvatar) VALUES 
                ('{0}', '{1}', '{2}', '{3}')
                """.format(phone, userId, userName, userAvatar))
        except:
            updateData = self.cursor.execute("""UPDATE kuaishouUsers 
                SET phone = '{0}', userName = '{1}', userAvatar = '{2}' 
                WHERE userId = '{3}'
                """.format(phone, userName, userAvatar, userId))

        self.db.commit()

    def delUsers(self, uid):
        self.cursor.execute("""DELETE FROM kuaishouUsers WHERE id={}""".format(uid))
        self.db.commit()

    def fetchUsers(self, limit = 500, page = 1):
        self.cursor.execute("""SELECT * FROM kuaishouUsers 
            ORDER BY id DESC LIMIT {0} OFFSET {1}""".format(limit, (page - 1)*limit))
        return self.cursor.fetchall()

    def fetchOneUsers(self, uid = 0):
        self.cursor.execute("""SELECT * FROM kuaishouUsers WHERE id={}""".format(uid))
        return self.cursor.fetchone()


    def addVideos(self, videoItem):
        self.cursor.execute("""INSERT INTO storageVideos(userId, userName, cityId, hashCode, 
            filePath, fileSize, title, cover) VALUES('{0}','{1}','{2}','{3}',
            '{4}','{5}','{6}','{7}')""".format(videoItem['userId'], videoItem['userName'], videoItem['cityId'], videoItem['hashCode'],
                videoItem['filePath'], videoItem['fileSize'], videoItem['title'], videoItem['cover']))
        self.db.commit()

    def delVideos(self, vid = 0, userId = 0, dropAll = False):
        if int(vid) > 0:
            wh = "id={}".format(vid)
        if int(userId) > 0:
            wh = "userId={}".format(userId)
        if dropAll:
            wh = "1=1"
        self.cursor.execute("""DELETE FROM storageVideos WHERE {}""".format(wh))
        self.db.commit()

    def fetchVideos(self, userId = 0, limit = 100, page = 1, keywords = ''):
        wh = '1=1'
        if int(userId) > 0:
            wh += ' AND userId={}'.format(userId)
        if keywords != '':
            wh += " AND title LIKE '{}%'".format(keywords)
        self.cursor.execute("""SELECT * FROM storageVideos WHERE {0} 
            ORDER BY id DESC LIMIT {1} OFFSET {2}""".format(wh, limit, (page - 1)*limit))
        return self.cursor.fetchall()

    def fetchOneVideos(self, vid = 0):
        self.cursor.execute("""SELECT * FROM storageVideos WHERE id={}""".format(vid))
        return self.cursor.fetchone()

    def countVideos(self, userId = 0, keywords = ''):
        wh = '1=1'
        if int(userId) > 0:
            wh += 'userId={}'.format(userId)
        if keywords != '':
            wh += " AND title LIKE '{}%'".format(keywords)
        self.cursor.execute("""SELECT COUNT(id) AS a FROM storageVideos WHERE {}""".format(wh))
        return self.cursor.fetchone()[0]

    def addVideospub(self, videoItem):
        try:
            self.cursor.execute("""INSERT INTO publishedVideos(userId, workId, title, cover, 
            uploadTime, playCount, likeCount, commentCount) VALUES('{0}','{1}','{2}','{3}',
            '{4}','{5}','{6}','{7}')""".format(videoItem['userId'], videoItem['workId'], videoItem['title'], videoItem['cover'],
                videoItem['uploadTime'], videoItem['playCount'], videoItem['likeCount'], videoItem['commentCount']))
        except:
            self.cursor.execute("""UPDATE publishedVideos SET playCount = '{0}', likeCount = '{1}', commentCount = '{2}' WHERE workId = '{3}'
                """.format(videoItem['playCount'], videoItem['likeCount'], videoItem['commentCount'], videoItem['workId']))
        self.db.commit()

    def delVideospub(self, vid = 0, userId = 0, dropAll = False):
        if int(vid) > 0:
            wh = "id={}".format(vid)
        if int(userId) > 0:
            wh = "userId={}".format(userId)
        if dropAll:
            wh = "1=1"
        self.cursor.execute("""DELETE FROM publishedVideos WHERE {}""".format(wh))
        self.db.commit()

    def fetchVideospub(self, userId = 0, limit = 100, page = 1, keywords = ''):
        wh = '1=1'
        if int(userId) > 0:
            wh += ' AND userId={}'.format(userId)
        if keywords != '':
            wh += " AND title LIKE '{}%'".format(keywords)
        self.cursor.execute("""SELECT * FROM publishedVideos WHERE {0} 
            ORDER BY id DESC LIMIT {1} OFFSET {2}""".format(wh, limit, (page - 1)*limit))
        return self.cursor.fetchall()

    def countVideospub(self, userId = 0, keywords = ''):
        wh = '1=1'
        if int(userId) > 0:
            wh += 'userId={}'.format(userId)
        if keywords != '':
            wh += " AND title LIKE '{}%'".format(keywords)
        self.cursor.execute("""SELECT COUNT(id) AS a FROM publishedVideos WHERE {}""".format(wh))
        return self.cursor.fetchone()[0]

    